/**
 * Device数据模型模块
 * model\sqlite\device.js
 */
 
//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");
 
/**
 * DeviceDB构造函数
 * @constructor
 */
function DeviceDB() {
  this.dbFile = config.dbfile;
  this.instance = null; //DeviceDB数据库实例
  this.db = null; //SQLite3实例
}
 
/**
 * 创建Dvice对象
 */
DeviceDB.getInstance = function () {
  if (!this.instance) {
    this.instance = new DeviceDB();
  }
  return this.instance;
};
 
/**
 * 连接数据库
 * @returns {Promise}
 */
DeviceDB.prototype.connect = function () {
  return new Promise((resolve, reject) => {
    this.db = new sqlite3.Database(this.dbFile, (err) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve("connect ok.");
      }
    });
  });
};
/**
 * 关闭数据库
 * @returns  {Promise}
 */
DeviceDB.prototype.close = function () {
  return new Promise((resolve, reject) => {
    this.db.close((err) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve("close ok.");
      }
    });
  });
};
 
/**
 * 获取指定ID的设备信息
 * @param {Number} deviceId 设备ID
 * @returns  {Promise}
 */
DeviceDB.prototype.find = function (deviceId) {
  return new Promise((resolve, reject) => {
    let sql = "SELECT * FROM lab_device WHERE id = ?";
    let params = [deviceId];
    this.db.get(sql, params, (err, result) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
 
/**
 * 获取设备信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns  {Promise}
 */
DeviceDB.prototype.findAll = function (limit = -1, offset = -1,orderBy="id",sort="DESC") {
  return new Promise((resolve, reject) => {
    let sql = "";
    let params = [];
    if (limit === -1) {
      sql = `SELECT * FROM lab_device ORDER BY ${orderBy} ${sort}`;
    } else if (offset === -1) {
      sql = `SELECT * FROM lab_device ORDER BY ${orderBy} ${sort} LIMIT ?`;
      params[0] = limit;
    } else {
      sql = `SELECT * FROM lab_device ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
      params[0] = limit;
      params[1] = offset;
    }
    this.db.all(sql, params, (err, result) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
 
/**
 * 新增设备
 * @param {Object} device 设备信息数据
 * @returns  {Promise}
 */
DeviceDB.prototype.add = function (device) {
  return new Promise((resolve, reject) => {
    let sql = `INSERT INTO  lab_device(
            title, imageurl, spcialtype, status, category, identify, 
            price, danwei, contact, phone, email, description, 
            xiamenId, technicalSpecs, mainFunctions, serviceContent, openSharingRules, created_time, 
            updated_time,brand
        ) VALUES (
            ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?,
            ?,?
        );`;
    let params = [
    device.title,
    device.imageurl,
    device.spcialtype,
    device.status,
    device.category,
    device.identify,
    device.price,
    device.danwei,
    device.contact,
    device.phone,
    device.email,
    device.description,
    device.xiamenId,
    device.technicalSpecs, 
    device.mainFunctions,
    device.serviceContent, 
    device.openSharingRules,
    device.createdTime,
    device.updatedTime,
    device.brand
    ];
    this.db.run(sql, params, function(err, result){
      if (err) {
        util.err(err);
        reject(err);
      } else {
        util.log(this);
        resolve(this.lastID); //插入的数据的自增ID
      }
    });
  });
};
 
/**
 * 修改设备
 * @param {Object} device 设备数据
 * @returns  {Promise}
 */
DeviceDB.prototype.update = function (device) {
  return new Promise((resolve, reject) => {
    let sql = `UPDATE lab_device SET
          title=?,imageurl=?,spcialtype=?,
          status=?,category=?,identify=?,price=?,
          danwei=?,contact=?,phone=?,email=?,description=?, xiamenId=?, 
          technicalSpecs=?, mainFunctions=?,
          serviceContent=?, openSharingRules=?,updated_time=?, brand=? WHERE id = ?`;
    let params = [
        device.title,
        device.imageurl,
        device.spcialtype,
        device.status,
        device.category,
        device.identify,
        device.price,
        device.danwei,
        device.contact,
        device.phone,
        device.email,
        device.description,
        device.xiamenId,
        device.technicalSpecs, 
        device.mainFunctions,
        device.serviceContent, 
        device.openSharingRules,
        device.updatedTime,  
        device.brand,
        device.id   
    ];
    this.db.run(sql, params, function(err, result){
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(this.changes); //影响的记录数
      }
    });
  });
};
 
/**
 * 删除设备
 * @param {Number} Id 设备ID
 * @returns  {Promise}
 */
DeviceDB.prototype.remove = function (Id) {
  return new Promise((resolve, reject) => {
    let sql = "DELETE FROM lab_device WHERE id = ?";
    let params = [Id];
    this.db.run(sql, params, function(err, result){
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(this.changes); //影响的记录数
      }
    });
  });
};


/**
 * 获取设备总数 
 * @returns  {Promise}
*/
DeviceDB.prototype.getCount = function(){
  return new Promise((resolve, reject) => {
    let sql = "SELECT count(1) AS total FROM lab_device";
    let params = [];
    this.db.get(sql, params, function (err, result) {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result); //影响的记录数
      }
    });
  });
};

	/**
 * 按关键字搜索
 * @param {String} q 搜索关键字
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns  {Promise}
 */
DeviceDB.prototype.search = function (q,limit = -1, offset = -1) {
  return new Promise((resolve, reject) => {
    let sql = "";
    q = `%${q}%`;
    let params = [q,q];
    if (limit === -1) {
      sql = "SELECT * FROM lab_device WHERE title LIKE ? OR serviceContent LIKE ?";
    } else if (offset === -1) {
      sql = "SELECT * FROM lab_device WHERE title LIKE ? OR serviceContent LIKE ? LIMIT ?";
      params.push(limit);
    } else {
      sql = "SELECT * FROM lab_device WHERE title LIKE ? OR serviceContent LIKE ? LIMIT ? OFFSET ?";
      params.push(limit);
      params.push(offset);
    }
    this.db.all(sql, params, (err, result) => {
      if (err) {
        util.err(err);
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
	/**
 * 获取指定ID的设备信息
 * @param {String} xiamenId 设备ID
 * @returns  {Promise}
 */
  DeviceDB.prototype.findByID = function (xiamenId) {
    return new Promise((resolve, reject) => {
      let sql = "SELECT * FROM lab_device WHERE xiamenId = ?";
      let params = [xiamenId];
      this.db.get(sql, params, (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  };

/**
 * 获取设备评价列表
 * @param {Number} deviceId 设备ID
 * @param {Number} limit 限制数量
 * @param {Number} offset 偏移量
 * @returns {Promise}
 */
DeviceDB.prototype.getDeviceFeedback = function(deviceId, limit = -1, offset = -1) {
  return new Promise((resolve, reject) => {
    let sql = `
      SELECT f.*, u.username 
      FROM device_feedback f
      LEFT JOIN users u ON f.userid = u.id
      WHERE f.deviceid = ?
      ORDER BY f.created_time DESC
    `;
    
    let params = [deviceId];
    
    if (limit !== -1) {
      sql += " LIMIT ?";
      params.push(limit);
    }
    if (offset !== -1) {
      sql += " OFFSET ?";
      params.push(offset);
    }

    this.db.all(sql, params, (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows);
      }
    });
  });
};

/**
 * 获取设备平均评分
 * @param {Number} deviceId 设备ID
 * @returns {Promise}
 */
DeviceDB.prototype.getDeviceAverageRating = function(deviceId) {
  return new Promise((resolve, reject) => {
    const sql = `
      SELECT 
        AVG(rating) as averageRating,
        COUNT(*) as totalRatings
      FROM device_feedback
      WHERE deviceid = ?
    `;
    
    this.db.get(sql, [deviceId], (err, row) => {
      if (err) {
        reject(err);
      } else {
        resolve({
          averageRating: row.averageRating || 0,
          totalRatings: row.totalRatings || 0
        });
      }
    });
  });
};

module.exports = DeviceDB;